<?php

class ExamData {

    public $conn;

    public function __construct() {
        $this->conn = Yii::app()->db;
    }

    /**
     * @ 取考试项目
     * @ yindebin
     * @ 2013-12-14
     * @ return array
     */
    static function _getExam($id = '', $status = '') {

        $where = ' AND "is_del" = 0';
        $order = '';

        $where .= (!empty($id)) ? ' AND "exam_id" = ' . $id : '';
        $where .= (!empty($status)) ? ' AND "exam_status" = ' . $status : ' AND "exam_status" != 0';

        $select = 'SELECT "exam_id","exam_name","exam_term_name","exam_term" FROM "{{exam}}"';
        $sql = " WHERE 1=1" . $where;
        $selectSql = $select . $sql . $order;

        if (!empty($id)) {
            $eduTermData = Yii::app()->db->createcommand($selectSql)->queryRow();
        } else {
            $eduTermData = Yii::app()->db->createcommand($selectSql)->queryAll();
        }

        return $eduTermData;
    }

    /**
     * @ 取所有的学年
     * @ yindebin
     * @ 2013-12-14
     * @ return array
     */
 static   public function _getGrade($id = '', $status = '') {
        #  取所有的学年  #
        $where = '';
        $order = '';

        $where .= (!empty($id)) ? ' AND "grade_id" = ' . $id : '';
        $order.= ' ORDER BY "term_start_year" ';

        $select = 'SELECT * FROM "{{edu_term}}"';
        $sql = " WHERE 1=1 " . $where;
        $selectSql = $select . $sql . $order;

        if (!empty($id)) {
            $eduTermData = Yii::app()->db->createcommand($selectSql)->queryRow();
        } else {
            $eduTermData = Yii::app()->db->createcommand($selectSql)->queryAll();
        }
        return $eduTermData;
    }

    /**
     * @ 取所有的学段
     * @ yindebin
     * @ 2013-12-14
     * @ return array
     */
 static   public function _getEduType($id = '', $status = '') {
        #  取所有学段  #
        $xdWhere = "";
        $xdOrder = "";

        $xdWhere .= (!empty($id)) ? ' AND "edu_id" =' . $id : '';
        $xdWhere .= (!empty($status)) ? ' AND "disabled" = ' . $status : ' AND "disabled" = 1 ';

        $xdWhere .= ' AND "disabled" = 1 AND "is_del" = 0 ';
        $xdSelect = 'SELECT * FROM "{{edu_type}}" ';
        $xdSql = " WHERE 1=1 " . $xdWhere;
        $xdSelectSql = $xdSelect . $xdSql . $xdOrder;

        if (!empty($id)) {
            $xdDatas = Yii::app()->db->createcommand($xdSelectSql)->queryRow();
        } else {
            $xdDatas = Yii::app()->db->createcommand($xdSelectSql)->queryAll();
        }

        return $xdDatas;
    }

    /**
     * @ 取所有的学校
     * @ yindebin
     * @ 2013-12-14
     * @ return array
     */
   static public function _getSchool($id = '', $status = '') {
        #  取所有学校  #
        $schoolWhere = "";
        $schoolOrder = "";

        $schoolWhere .= (!empty($id)) ? ' AND "school_id" = ' . $id : '';
        $schoolWhere .= (!empty($status)) ? ' AND "disabled" = ' . $status : ' AND "disabled" = 1';

        $schoolWhere .= ' AND "disabled" = 1';
        $schoolSelect = 'SELECT * FROM "{{school}}" ';
        $schoolSql = ' WHERE 1=1 AND "is_del"=0 ' . $schoolWhere;
        $schoolSelectSql = $schoolSelect . $schoolSql . $schoolOrder;

        if (!empty($id)) {
            $schoolDatas = Yii::app()->db->createcommand($schoolSelectSql)->queryRow();
        } else {
            $schoolDatas = Yii::app()->db->createcommand($schoolSelectSql)->queryAll();
        }

        return $schoolDatas;
    }
	static public function _getSchoolList($school_id='') {
        #  取所有学校  #
        $schoolWhere = "";
        $schoolOrder = "";
        $schoolWhere .= (!empty($school_id)) ? ' AND "school_id" IN (' . $school_id . ')' : '';
        $schoolWhere .= ' AND "disabled" = 1';
        $schoolSelect = 'SELECT * FROM "{{school}}" ';
        $schoolSql = ' WHERE 1=1 AND "is_del"=0 ' . $schoolWhere;
        $schoolSelectSql = $schoolSelect . $schoolSql . $schoolOrder;
        if (!empty($id)) {
            $schoolDatas = Yii::app()->db->createcommand($schoolSelectSql)->queryRow();
        } else {
            $schoolDatas = Yii::app()->db->createcommand($schoolSelectSql)->queryAll();
        }
        return $schoolDatas;
    }

    /**
     * @ 取考试项目学校
     * @ yindebin
     * @ param $exam_id
     * @ 2013-12-14
     * @ return array
     */
 static   public function _getExamSchool($exam_id) {
        if (empty($exam_id))
            return null;

        $examSql = 'SELECT * FROM "{{exam_school}}" WHERE "exam_id" = ' . $exam_id;
        $school_id = Yii::app()->db->createCommand($examSql)->queryAll();

        if (!empty($school_id)) {
            $school_data = $temp = array();
            foreach ($school_id as $value) {

                if (!empty($temp [$value ['school_id']])) {

                    $school_data [] = $temp [$value ['school_id']];
                } else {

                    $schoolSql = 'SELECT "school_id","school_name" FROM "{{school}}" WHERE "school_id" = :school_id';
                    $result = Yii::app()->db->createCommand($schoolSql);
                    $result->bindParam(':school_id', $value ['school_id'], PDO::PARAM_STR);
                    $temp [$value ['school_id']] = $school_data [] = $result->queryRow();
                }
            }
            return $school_data;
        } else {
            return null;
        }
    }

    /**
     * @ 取地区学校
     * @ yindebin
     * @ param $region_id
     * @ 2013-12-14
     * @ return array
     */
  static  public function _getRegionSchool($parent_id = '', $region_id = '') {
        if (empty($parent_id) && empty($region_id))
            return null;

        $where = "";

        if (!empty($parent_id)) {
            $where .= ' AND "region_parent_id" = ' . $parent_id;
        }

        if (!empty($region_id)) {
            $where .= ' AND "region_id" = ' . $region_id;
        }

        $examSql = 'SELECT * FROM "{{school}}" WHERE 1=1 ' . $where;

        $school_data = Yii::app()->db->createCommand($examSql)->queryAll();

        return $school_data;
    }

    /**
     * @ 取学校
     * @ yindebin
     * @ param $school_code
     * @ 2013-12-14
     * @ return array
     */
  static  public function _getCodeSchool($school_code) {
        if (empty($school_code))
            return null;

        #  取所有学校  #
        $schoolWhere = "";
        $schoolOrder = "";

        $schoolWhere .= ' AND "school_code" = \'' . $school_code . "'";

        $schoolSelect = 'SELECT "school_name","school_id","school_code" FROM "{{school}}" ';
        $schoolSql = " WHERE 1=1 " . $schoolWhere;
        $schoolSelectSql = $schoolSelect . $schoolSql . $schoolOrder;

        $schoolDatas = Yii::app()->db->createcommand($schoolSelectSql)->queryRow();

        return $schoolDatas;
    }

    /**
     * @ 取考试项目科目
     * @ yindebin
     * @ param $exam_id
     * @ 2013-12-14
     * @ return array
     */
    public static function _getExamSubject($exam_id) {
        if (empty($exam_id))
            return null;

        $examSql = 'SELECT * FROM "{{exam}}" WHERE "exam_id" = :exam_id ';
        $result = Yii::app()->db->createCommand($examSql);
        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $exam = $result->queryRow();

        $temp = array();
        if (!empty($exam ['exam_subject_ids'])) {

            $subject_id = explode('-', $exam ['exam_subject_ids']);
            foreach ($subject_id as $sv) {
                $tsql = 'SELECT "subject_id","subject_name","subject_code" FROM "{{subject}}" WHERE "subject_code" = :subject_code';
                $result = Yii::app()->db->createCommand($tsql);
                $result->bindParam(":subject_code", $sv, PDO::PARAM_STR);
				$data = $result->queryRow ();
				if(!empty($data)){
					$temp[] = $data;
				}
			}
		}
        
		return $temp;
	}
	
	/**
	 * @ 取考试分数
	 * @ yindebin
	 * @ param $exam_id
	 * @ param $subject_id
	 * @ param $school_id
	 * @ 2013-12-14
	 * @ return array
	 */
static	public function _getExamPoint($subject_id, $school_id, $exam_id) {
		if (empty ( $exam_id ) ||  empty ( $subject_id ))
			return null;


        $where = ' AND "school_id" IN (:school_id) AND "exam_id" = :exam_id AND "subject_id" = :subject_id  ';

        $point = 'SELECT "score" FROM "{{point}}" WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($point);

        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':school_id', $school_id, PDO::PARAM_STR);
        $result->bindParam(':subject_id', $subject_id, PDO::PARAM_STR);

        return $pointData = $result->queryAll();
    }

    /**
     * @ 取考试分数
     * @ yindebin
     * @ param $exam_id
     * @ param $subject_id
     * @ param $school_id
     * @ 2013-12-14
     * @ return array
     */
  static  public function _getRegionPoint($school_id, $exam_id) {
        if (empty($exam_id) || empty($school_id))
            return null;

        $where = ' AND "school_id" = :school_id AND "exam_id" = :exam_id ';

        $point = ' SELECT "score" FROM "{{point}}" WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($point);

        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':school_id', $school_id, PDO::PARAM_STR);

        return $pointData = $result->queryAll();
    }

    /**
     * @ 取考试分数
     * @ yindebin
     * @ param $exam_id
     * @ param $student_id
     * @ param $school_id
     * @ 2013-12-14
     * @ return array
     */
  static  public function _getStudentPoint($exam_id, $student_id, $school_id = '') {
        if (empty($exam_id) || empty($student_id))
            return null;

        $where = ' AND "exam_id" = :exam_id AND "student_id" = :student_id  ';

        if (!empty($school_id)) {
            $where .= ' AND "school_id" = ' . $school_id;
        }
        $point = 'SELECT SUM("score") AS "score_sum" FROM "{{point}}" WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($point);

        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':student_id', $student_id, PDO::PARAM_STR);

        return $pointData = $result->queryRow();
    }

    /**
     * @ 取考试分数段设置
     * @ yindebin
     * @ param $exam_id
     * @ param $subject_id
     * @ 2013-12-14
     * @ return array score grading_id grading_name
     */
   static public function _getPointGrading($subject_id, $exam_id) {
        if (empty($exam_id) || empty($subject_id))
            return null;

        $where = ' AND "exam_id" = :exam_id AND "subject_id" = :subject_id  ';

        $grading = 'SELECT "grading_id","score" FROM "{{point_grading}}" WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($grading);

        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':subject_id', $subject_id, PDO::PARAM_STR);

        $gradingData = $result->queryAll();

        if (!empty($gradingData)) {

            foreach ($gradingData as $key => $value) {
                $temp = $this->_getGrading($value ['grading_id']);
                $gradingData [$key] ['grading_name'] = $temp ['grading_name'];
            }
        }

        return $gradingData;
    }

    /**
     * @ 取所有的科目
     * @ yindebin
     * @ 2013-12-14
     * @ return array
     */
   static public function _getSubject($id = '', $status = '') {
        #  取所有科目  #
        $subjectWhere = ' AND "is_del" = 0 ';
        $subjectOrder = "";

        $subjectWhere .= (!empty($id)) ? ' AND "subject_id" = ' . $id : '';
        $subjectWhere .= (!empty($status)) ? ' AND "disabled" = ' . $status : ' AND "disabled" = 1';

        $subjectSelect = ' SELECT * FROM "{{subject}}" ';
        $subjectSql = " WHERE 1=1 " . $subjectWhere;
        $subjectSelectSql = $subjectSelect . $subjectSql . $subjectOrder;

        if (!empty($id)) {
            $subjectDatas = Yii::app()->db->createcommand($subjectSelectSql)->queryRow();
        } else {
            $subjectDatas = Yii::app()->db->createcommand($subjectSelectSql)->queryAll();
        }

        return $subjectDatas;
    }

    static public function _getSubjectList($id = '', $status = '') {
        #  取所有科目  #
        $subjectWhere = ' AND "is_del" = 0 ';
        $subjectOrder = "";
        $subjectWhere .= (!empty($id)) ? " AND \"subject_id\" IN( " . $id . ")" : '';
        $subjectWhere .= (!empty($status)) ? ' AND "disabled" = ' . $status : ' AND "disabled" = 1';
        $subjectSelect = ' SELECT * FROM "{{subject}}" ';
        $subjectSql = " WHERE 1=1 " . $subjectWhere;
        $subjectSelectSql = $subjectSelect . $subjectSql . $subjectOrder;
        $subjectDatas = Yii::app()->db->createcommand($subjectSelectSql)->queryAll();
        return $subjectDatas;
    }
    /**
     * @ 根据教育类型取年级
     * @ yindebin
     * @ 2013-12-14 AM
     * @ return array

      public function _getEduGrade($id = '', $status = '') {
      $Datas = array ();
      $Where = "";
      $Order = "";

      $Where .= (! empty ( $id )) ? " AND grade_id = '" . $id . "'" : '';

      $Select = "SELECT * FROM {{edu_grade}}";
      $Sql = " WHERE 1 " . $Where;
      $SelectSql = $Select . $Sql . $Order;
      if (! empty ( $id )) {
      $Datas = Yii::app ()->db->createcommand ( $SelectSql )->queryRow ();
      } else {
      $Datas = Yii::app ()->db->createcommand ( $SelectSql )->queryAll ();
      }
      return $Datas;
      }
     */

    /**
     * @ 根据教育类型取年级
     * @ yindebin
     * @ 2013-12-14 AM
     * @ return array
     */
  static  public function _getEduGrade($edu_id) {
        if (empty($edu_id))
            return null;
        $Datas = array();
        $Where = "";
        $Order = "";

        $Where .= ' AND "disabled" = 1 AND "is_del" = 0 AND "edu_id" = ' . $edu_id;

        $Select = ' SELECT * FROM "{{edu_grade}}" ';
        $Sql = " WHERE 1=1 " . $Where;
        $SelectSql = $Select . $Sql . $Order;

        $Datas = Yii::app()->db->createcommand($SelectSql)->queryAll();

        return $Datas;
    }

    /**
     * @ 根据教育类型更新年级显示状态
     * @ yindebin
     * @ 2013-12-14 AM
     * @ return array
     */
  static  public function _updateEduGrade($edu_id = '', $grade_id = '') {
        if (empty($edu_id) && empty($grade_id))
            return null;

        if (!empty($edu_id)) {
            $sql = 'UPDATE "{{edu_grade}}" SET "disabled" = 0 WHERE "edu_id" = ' . $edu_id;
        } elseif (!empty($grade_id)) {
            $sql = 'UPDATE "{{edu_grade}}" SET "disabled" = 1 WHERE "grade_id" = ' . $grade_id;
        } else {
            return null;
        }

        $result = Yii::app()->db->createcommand($sql)->execute();

        return $result;
    }

    /**
     * @ 取所有等级
     * @ yindebin
     * @ 2013-12-14 AM
     * @ return array
     */
   static public function _getGrading($grading_id = '') {
        $where = "";

        $where .= (!empty($grading_id)) ? ' AND "id" = ' . $grading_id : '';

        $sql = 'SELECT * FROM "{{grading}}" WHERE 1=1 ' . $where;

        if (!empty($grading_id)) {
            $grading = Yii::app()->db->createcommand($sql)->queryRow();
        } else {
            $grading = Yii::app()->db->createcommand($sql)->queryAll();
        }

        return $grading;
    }

    /**
     * @ 取学生信息
     * @ yindebin
     * @ param $exam_id
     * @ param $school_id
     * @ 2013-12-16
     */
   static public function _getStuden($exam_id, $disciplin_id, $school_code = '') {
        $where = "";
        $order = "";

        $where .= ' AND "exam_id" = :exam_id AND "disciplin_id" = :disciplin_id ';
        if (!empty($school_code)) {
            $where .= ' AND "school_code" = ' . $school_code;
        }

        $sql = ' SELECT "student_name","student_id","school_code" FROM "{{student}}" WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($sql);
        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':disciplin_id', $disciplin_id, PDO::PARAM_STR);

        $data = $result->queryAll();

        return $data;
    }

    /**
     * @ 取本一本二线
     * @ yindebin
     * @ param $exam_id
     * @ param $disciplin_type
     * @ 2013-12-16
     */
   static public function _getEduScore($exam_id, $disciplin_type) {
        $where = "";
        $order = "";

        $where .= ' AND "exam_id" = :exam_id AND "disciplin_type" = :disciplin_type';

        $sql = 'SELECT "score_type1","score_type2","score_type3" FROM {{edu_score}} WHERE 1=1 ' . $where;

        $result = Yii::app()->db->createCommand($sql);
        $result->bindParam(':exam_id', $exam_id, PDO::PARAM_STR);
        $result->bindParam(':disciplin_type', $disciplin_type, PDO::PARAM_STR);

        $data = $result->queryRow();

        return $data;
    }

    /**
     * @ 取地区
     * @ param parent_id
     * @ yindebin
     * @ 2013-12-16
     * @ return arry
     */
    static public function _getRegion($parent_id = '') {
        $where = "";
        $order = "";

        if (empty($parent_id)) {
            $where .= ' AND "parent_id" = 0';
        } else {
            $where .= ' AND "parent_id" = ' . $parent_id;
        }
        $sql = 'SELECT * FROM "{{region}}" WHERE 1=1 ' . $where;
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }
    
    static public function _getQObject(){
    	$data = array();
    	
    	$sql = 'SELECT "question_code","question_name" FROM "{{question_type}}"';
    	$result = Yii::app()->db->createCommand($sql);
    	$row = $result->queryAll();
    	if(!empty($row)){
    		foreach($row as $value){
    			$data[$value['question_code']] = $value['question_name'];
    		}
    	}
    	return $data;
    }
	
	/**
     * @ 取科别
     * @ param disciplin_id
     * @ yindebin
     * @ 2013-12-16
     * @ return arry
     */
	static public function _getDisciplin($disciplin_id=''){
		$data = array();
		if(empty($disciplin_id)){
			$sql = 'SELECT "disciplin_code","disciplin_nick","disciplin_name","disciplin_id","disciplin_type" FROM "{{disciplin}}" where 1=1 AND "is_del" = 0';
			$data = Yii::app()->db->createCommand($sql)->queryAll();
		}else{
			$sql = 'SELECT "disciplin_code","disciplin_nick","disciplin_name","disciplin_id","disciplin_type" FROM "{{disciplin}}" where "disciplin_id" = '.$disciplin_id;
			$data = Yii::app()->db->createCommand($sql)->queryRow();
		}
		
		return $data;
	}
}